package com.dingding.manager.modules.system.sql;

/**
* @ClassName: OrganizationSQL 
* @Description: 机构管理sql
* @author SUNZHICHENG 
* @date 2016年12月4日 上午11:47:55 
* @version V2.6.2
 */
public class OrganizationSQL {
	
	/**
	 * 按用户名查询所属机构
	 */
	public static final String QUERY_USER_ORGANIZATION_BY_USERNAME=" select b.* from dd_sys_user a , dd_organization b where a.companyId = b.organizationId AND b.organizationStatus = 1 AND a.loginName = ? ";
	
	/**
	 * 获取所有机构
	 */
	
	public static final String QUERY_ORGANIZATION_LIST=" SELECT * FROM dd_organization m where m.delFlag = 0 AND m.organizationStatus = 1 ";
	

	
	/**
	 * 公司详情
	 */
	public static final String ORGANIZATION_INFO=" select a.organizationId organizationId, a.parentId parentId, e.provice_id proviceId, e.provice_name proviceName,c.sysCityId sysCityId,c.cityName cityName,a.organizationName organizationName, a.organizationAddr organizationAddr,a.businessLicence businessLicence,a.contacts contacts,a.contactsNumber contactsNumber,a.contactsEmail contactsEmail, DATE_FORMAT(a.licenseStartDate,'%Y-%m-%d %H:%I:%S') licenseStartDate, DATE_FORMAT(a.licenseEndDate,'%Y-%m-%d %H:%I:%S') licenseEndDate, a.organizationType organizationType,a.organizationStatus organizationStatus,a.remarks remarks,DATE_FORMAT(a.createTime,'%Y-%m-%d %H:%I:%S') createTime, DATE_FORMAT(a.updateTime,'%Y-%m-%d %H:%I:%S') updateTime,a.createUserId createUserId, a.updateUserId updateUserId from dd_organization a, dd_city_ref_organization b, dd_sys_city c ,dd_sys_provice_city d ,dd_sys_province e  where a.organizationId = b.organizationId and b.cityId = c.sysCityId  and c.sysCityId = d.sysCityId and d.proviceId = e.provice_id and a.delFlag = 0 AND a.organizationId =  ? ";
	
	/**
	 * 根据公司名称获取公司(不等于要修改的机构)
	 */
	public static final String ORGANIZATION_BY_NAME=" SELECT * FROM dd_organization a WHERE a.organizationName = ?  ";

	
	/**
	 * 根据用户id查询机构信息
	 */
	public static final String QUERY_ORGANIZATION_BY_USERID=" SELECT o.`businessLicence`, o.`cityId`,o.`cityName`,o.`contacts`,o.`contactsEmail`,o.`contactsNumber`,o.`contactsNumber`,o.`createTime`,o.`createUserId`,o.`delFlag`,o.`licenseEndDate`,o.`licenseStartDate`,o.`organizationAddr`,o.`organizationId`,o.`organizationName`,o.`organizationStatus`,o.`organizationType`,o.`parentId`,o.`parentIds`,o.`remarks`,o.`updateTime`,o.`updateUserId` FROM `dd_organization` o LEFT JOIN `dd_sys_user` u ON o.`organizationId` = u.`companyId` WHERE u.`sysUserId` = ?";

	
	/**
	 * 根据机构id查询机构城市关联信息
	 */
	public static final String QUERY_ORGANIZATION_CITY=" SELECT * FROM dd_city_ref_organization a where a.organizationId = ? ";
	
}
